package test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class LeagueDBquery {
	
	private Connection connection = null;
	private Statement stmt = null;
	
	//constructor
	public LeagueDBquery() throws SQLException {
		connection = DriverManager.getConnection("jdbc:mysql://db4free.net:3306/leaguedata", "csbendercs", "ics321");
		stmt = connection.createStatement();
	}
	
	public ArrayList<String> query(String name, String clas, int stun, int fear, int disable, int slow, int root, int knockup, int shield, int heal, int msbuff, int asbuff, 
			                           int invul, int untarget, int invis, int dash, int displace, int silence, int revive, int mitigation, int atkbuff) throws SQLException {
		
		ArrayList<String> results = new ArrayList<String>();
		StringBuilder query = new StringBuilder();
		StringBuilder attributes = new StringBuilder();
		String original = "Select distinct t1.name From Hierarchy as t2, mastertable as t1 ";
		query.append("Select distinct t1.name From Hierarchy as t2, mastertable as t1 ");
		ResultSet rs;
		ResultSetMetaData rsmd;
		
		if(!name.equals("")) {
                    query.append("Where t1.name='" + name + "'");
		}
		if(!clas.equals("")) {
                    if(query.toString().equals(original)) {
                        query.append("Where (t2.primary='" + clas + "' or " + "t2.secondary='" + clas + "') and t2.name=t1.name");
                    }
                    else {
                        query.append(" and (t2.primary='" + clas + "' or " + "t2.secondary=" + clas + "') and t2.name=t1.name");
                    }
                }
		if(stun==1) {
			if(query.toString().equals(original)) {
				query.append("Where t1.stun=1");
			}
			else {
			  query.append(" and t1.stun=1");
			}
			attributes.append(" stun");
		}
		if(fear==1) {
			if(query.toString().equals(original)) {
				query.append("Where fear=1");
			}
			else {
			  query.append(" and fear=1");
			}
			attributes.append(" fear");
		}
		if(disable==1) {
			if(query.toString().equals(original)) {
				query.append("Where disable=1");
			}
			else {
			  query.append(" and disable=1");
			}
			attributes.append("Where disable");
		}
		if(slow==1) {
			if(query.toString().equals(original)) {
				query.append("Where slow=1");
			}
			else {
			  query.append(" and slow=1");
			}
			attributes.append(" slow");
		}
		if(root==1) {
			if(query.toString().equals(original)) {
				query.append("Where root=1");
			}
			else {
			  query.append(" and root=1");
			}
			attributes.append(" root");
		}
		if(knockup==1) {
			if(query.toString().equals(original)) {
				query.append("Where knockup=1");
			}
			else {
			  query.append(" and knockup=1");
			}
			attributes.append(" knockup");
		}
		if(shield==1) {
			if(query.toString().equals(original)) {
				query.append("Where shield=1");
			}
			else {
			  query.append(" and shield=1");
			}
			attributes.append(" shield");
		}
		if(heal==1) {
			if(query.toString().equals(original)) {
				query.append("Where heal=1");
			}
			else {
			  query.append(" and heal=1");
			}
			attributes.append(" heal");
		}
		if(msbuff==1) {
			if(query.toString().equals(original)) {
				query.append("Where msbuff=1");
			}
			else {
			  query.append("and msbuff=1");
			}
			attributes.append("Where msbuff");
		}
		if(asbuff==1) {
			if(query.toString().equals(original)) {
				query.append("Where asbuff=1");
			}
			else {
			  query.append(" and asbuff=1");
			}
			attributes.append(" asbuff");
		}
		if(invul==1) {
			if(query.toString().equals(original)) {
				query.append("Where invul=1");
			}
			else {
			  query.append(" and invul=1");
			}
			attributes.append(" invul");
		}
		if(untarget==1) {
			if(query.toString().equals(original)) {
				query.append("Where untarget=1");
			}
			else {
			  query.append(" and untarget=1");
			}
			attributes.append(" untarget");
		}
		if(invis==1) {
			if(query.toString().equals(original)) {
				query.append("Where invis=1");
			}
			else {
			  query.append(" and invis=1");
			}
			attributes.append(" invis");
		}
		if(dash==1) {
			if(query.toString().equals(original)) {
				query.append("Where dash=1");
			}
			else {
			  query.append(" and dash=1");
			}
			attributes.append(" dash");
		}
		if(displace==1) {
			if(query.toString().equals(original)) {
				query.append("Where displace=1");
			}
			else {
			  query.append(" and displace=1");
			}
			attributes.append(" displace");
		}
		if(silence==1) {
			if(query.toString().equals(original)) {
				query.append("Where silence=1");
			}
			else {
			  query.append(" and silence=1");
			}
			attributes.append(" silence");
		}
		if(revive==1) {
			if(query.toString().equals(original)) {
				query.append("Where revive=1");
			}
			else {
			  query.append(" and revive=1");
			}
		}
		if(mitigation==1) {
			if(query.toString().equals(original)) {
				query.append("Where mitigation=1");
			}
			else {
			  query.append(" and mitigation=1");
			}
		}
		if(atkbuff==1) {
			if(query.toString().equals(original)) {
				query.append("Where atkbuff=1");
			}
			else {
			  query.append(" and atkbuff=1");
			}
		}
		
		
		rs = stmt.executeQuery(query.toString());
		rsmd = rs.getMetaData();
		int columnsNumber = rsmd.getColumnCount();
		
		while (rs.next() ) {
			StringBuilder result = new StringBuilder();
			result.append(rs.getString(1));
			results.add(result.toString());
		}
		return results;
	}
	public ArrayList<String> attFind(ArrayList<String> names) throws SQLException{
		ArrayList<String> results = new ArrayList<String>();
		
		for(int i = 0; i < names.size(); i++) {
			
			ResultSet rs = stmt.executeQuery("Select t1.name, t1.primary, t1.secondary, t2.stun, t2.fear, t2.disable, t2.slow, t2.root, t2.knockup, t2.shield, t2.heal, t2.msbuff, t2.asbuff, t2.invul, t2.invis, t2.untarget, t2.dash, t2.displace, t2.silence, t2.revive, t2.mitigation, t2.atkbuff "
					+ "From Hierarchy as t1, mastertable as t2 " + 
					"Where t1.name='" + names.get(i) + "' and t2.name='" + names.get(i) + "'"); 
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnsNumber = rsmd.getColumnCount();
			rs.next();
			
			
			
			StringBuilder result = new StringBuilder();
			for (int ii = 1; ii <= 3; ii++) {
				result.append(" " + rs.getString(ii));
			}
			if(rs.getInt(4)==1) {
				result.append(" stun");
			}
			if(rs.getInt(5)==1) {
				result.append(" fear");
			}
			if(rs.getInt(6)==1) {
				result.append(" disable");
			}
			if(rs.getInt(7)==1) {
				result.append(" slow");
			}
			if(rs.getInt(8)==1) {
				result.append(" root");
			}
			if(rs.getInt(9)==1) {
				result.append(" knockup");
			}
			if(rs.getInt(10)==1) {
				result.append(" shield");
			}
			if(rs.getInt(11)==1) {
				result.append(" heal");
			}
			if(rs.getInt(12)==1) {
				result.append(" msbuff");
			}
			if(rs.getInt(13)==1) {
				result.append(" asbuff");
			}
			if(rs.getInt(14)==1) {
				result.append(" invul");
			}
			if(rs.getInt(15)==1) {
				result.append(" untarget");
			}
			if(rs.getInt(16)==1) {
				result.append("  invis");
			}
			if(rs.getInt(17)==1) {
				result.append(" dash");
			}
			if(rs.getInt(18)==1) {
				result.append(" silence");
			}
			if(rs.getInt(19)==1) {
				result.append(" revive");
			}
			if(rs.getInt(20)==1) {
				result.append(" mitigation");
			}
			if(rs.getInt(21)==1) {
				result.append(" atkbuff");
			}
			
				results.add(result.toString());
		}
		
		return results;
	}
}
